const nodemailer = require('nodemailer')
const { querySql, startTransaction, commitTransaction, rollbackTransaction } = require('../utils/index');
const { emailConfig, getCode } = require('../utils/config')
const boom = require('boom');
const {
  CODE_ERROR,
  CODE_SUCCESS,
  PRIVATE_KEY,
  JWT_EXPIRED
} = require('../utils/constant');
function sendResponse(res, code, msg, data = null) {
  res.json({ code, msg, data });
}
const sessionEmail = {}; // 用于存储验证码
async function handleCommonUpload(req, res) {
  try {
    if (!req.file) {
      return sendResponse(res, CODE_ERROR, '请选择有效的图片文件');
    }
    // 生成完整URL
    const avatarUrl = `${req.protocol}://${req.get('host')}/public/common/${req.file.filename}`;
    return sendResponse(res, CODE_SUCCESS, '上传成功', {
      url: avatarUrl
    });
  } catch (error) {
    console.error('上传错误:', error);
    return sendResponse(res, CODE_ERROR, '服务器处理文件时出错');
  }
}
async function sendEmail(req, res, next) {
  try {
    const { email } = req.query;
    // 生成 6 位随机验证码
    const verificationCode = getCode(6);
    console.log(email, verificationCode)
    // 创建邮件传输器
    let transporter = nodemailer.createTransport(emailConfig);
    // 邮件信息
    let mailObj = {
      from: `来自<${emailConfig.auth.user}>`, // 发送方邮箱及标题
      to: email, // 对方邮箱地址
      subject: '密码找回 - Zero验证码', // 邮件主题
      html: `
          <div style="font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Helvetica, Arial, sans-serif; max-width: 500px; margin: 0 auto; padding: 20px; color: #333333;">
            <!-- Simple Header -->
            <div style="border-bottom: 2px solid #f0f0f0; padding-bottom: 15px; margin-bottom: 20px;">
              <h1 style="color: #333333; font-size: 22px; font-weight: 500; margin: 0;">Zero 密码找回</h1>
            </div>
            
            <!-- Content -->
            <div style="padding: 0 5px;">
              <p style="font-size: 15px; line-height: 1.6; margin: 0 0 20px;">
                您好，
              </p>
              <p style="font-size: 15px; line-height: 1.6; margin: 0 0 25px;">
                您正在找回 Zero 账号的密码，请使用以下验证码完成验证：
              </p>
              
              <!-- Verification Code -->
              <div style="margin: 25px 0; text-align: center;">
                <div style="display: inline-block; background-color: #f7f7f7; border: 1px solid #eeeeee; border-radius: 4px; padding: 14px 30px;">
                  <span style="font-family: Consolas, monospace; font-size: 24px; font-weight: 600; letter-spacing: 2px; color: #2c2c2c;">
                    ${verificationCode}
                  </span>
                </div>
              </div>
              
              <p style="font-size: 15px; line-height: 1.6; margin: 25px 0 0;">
                验证码有效期为 <span style="font-weight: 600;">10 分钟</span>，请尽快使用。
              </p>
              
              <p style="font-size: 14px; color: #777777; margin: 30px 0 0; padding-top: 15px; border-top: 1px solid #f0f0f0;">
                如非本人操作，请忽略该邮件。
              </p>
            </div>
            
            <!-- Simple Footer -->
            <div style="margin-top: 30px; padding-top: 15px; border-top: 1px solid #f0f0f0; text-align: center;">
              <p style="font-size: 13px; color: #999999; margin: 0;">
                此邮件由系统自动发送，请勿回复
              </p>
            </div>
          </div>
        `,
    };
    // 发送邮件
    transporter.sendMail(mailObj, (err, info) => {
      if (err) {
        console.log('发邮件出错了', err);
        return sendResponse(res, CODE_ERROR, '发邮件出错了');
      } else {
        // 将验证码存入 sessionEmail，关联邮箱
        sessionEmail[email] = verificationCode;
        console.log('验证码已存入 sessionEmail', sessionEmail);
        // 返回成功响应
        console.log('邮件发送成功');
        return sendResponse(res, CODE_SUCCESS, '发送验证码成功');
      }
    });
  } catch (err) {
    return sendResponse(res, CODE_ERROR, err.message);
  }
}
async function validateCode(req, res) {
  try {
    const { email, code } = req.query;
    // 检查邮箱和验证码是否为空
    if (!email || !code) {
      return sendResponse(res, CODE_ERROR, '邮箱和验证码不能为空');
    }
    // 获取存储的验证码
    const storedCode = sessionEmail[email];
    console.log('保存的验证码', storedCode)
    // 验证码不存在或已过期
    if (!storedCode) {
      return sendResponse(res, CODE_ERROR, '验证码已过期，请重新获取');
    }
    // 验证码匹配
    if (storedCode === code) {
      // 验证成功后删除验证码
      delete sessionEmail[email];
      return sendResponse(res, CODE_SUCCESS, '验证码正确');
    } else {
      return sendResponse(res, CODE_ERROR, '验证码错误');
    }
  } catch (err) {
    return sendResponse(res, CODE_ERROR, '服务器验证时出错');
  }
}
async function getStatistics(req, res) {
  try {
    const sql = 'SELECT * FROM statistics WHERE id = 1'
    const stats = await querySql(sql,[])
    console.log(stats.affectedRows)
    if (stats) {
      sendResponse(res, CODE_SUCCESS, '查询成功', {
        post_count: stats[0].post_count,
        user_count: stats[0].user_count,
        comment_count: stats[0].comment_count,
        like_count: stats[0].like_count,
        updated_at: stats[0].updated_at
      });
    } else {
      sendResponse(res, CODE_ERROR, '查询失败');
    }
  }
  catch (error) {
    next(boom.badImplementation(error.message));
  }
}
/**
 * 获取用户注册趋势（按月统计）
 */
async function getUserGrowthTrend(req, res, next) {
  try {
    const { startDate, endDate } = req.body;

    // 默认查询过去 12 个月的数据
    const defaultEndDate = new Date();
    const defaultStartDate = new Date();
    defaultStartDate.setMonth(defaultStartDate.getMonth() - 12);

    const start = startDate || defaultStartDate.toISOString().split('T')[0];
    const end = endDate || defaultEndDate.toISOString().split('T')[0];

    // 查询每月用户注册数量
    const sql = `
      SELECT 
        months.month,
        IFNULL(COUNT(users.id), 0) AS count
      FROM (
        SELECT DATE_FORMAT(DATE_SUB(?, INTERVAL seq MONTH), '%Y-%m') AS month
        FROM (
          SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
          UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
          UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          UNION ALL SELECT 10 UNION ALL SELECT 11
        ) AS seq_table
        WHERE DATE_SUB(?, INTERVAL seq MONTH) >= ?
      ) AS months
      LEFT JOIN users ON DATE_FORMAT(users.created_at, '%Y-%m') = months.month
      GROUP BY months.month
      ORDER BY months.month ASC
    `;
    const result = await querySql(sql, [end, end, start]);

    // 格式化数据
    const labels = [];
    const data = [];
    result.forEach(row => {
      labels.push(row.month);
      data.push(row.count);
    });

    sendResponse(res, CODE_SUCCESS, '获取用户注册趋势成功', {
      labels,
      data
    });
  } catch (error) {
    next(boom.badImplementation(error.message));
  }
}
/**
 * 获取帖子发布趋势（按月统计）
 */
async function getPostPublishTrend(req, res, next) {
  try {
    const { startDate, endDate } = req.body;

    // 1. 修正默认日期范围（确保跨年计算正确）
    const defaultEndDate = new Date();
    const defaultStartDate = new Date();
    defaultStartDate.setFullYear(defaultEndDate.getFullYear(), defaultEndDate.getMonth() - 11, 1); // 过去 12 个月（含当月）

    const start = startDate || defaultStartDate.toISOString().split('T')[0];
    const end = endDate || defaultEndDate.toISOString().split('T')[0];

    // 2. 使用子查询生成连续月份列表，并左关联帖子数据
    const sql = `
      SELECT 
        months.month,
        IFNULL(COUNT(posts.postId), 0) AS count
      FROM (
        SELECT DATE_FORMAT(DATE_SUB(?, INTERVAL seq MONTH), '%Y-%m') AS month
        FROM (
          SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
          UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
          UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          UNION ALL SELECT 10 UNION ALL SELECT 11
        ) AS seq_table
        WHERE DATE_SUB(?, INTERVAL seq MONTH) >= ?
      ) AS months
      LEFT JOIN posts ON DATE_FORMAT(posts.created_at, '%Y-%m') = months.month
      GROUP BY months.month
      ORDER BY months.month ASC
    `;
    const result = await querySql(sql, [end, end, start]);

    // 3. 格式化数据（确保月份顺序和零填充）
    const labels = [];
    const data = [];
    result.forEach(row => {
      labels.push(row.month);
      data.push(row.count);
    });

    sendResponse(res, CODE_SUCCESS, '获取帖子发布趋势成功', {
      labels,
      data
    });
  } catch (error) {
    next(boom.badImplementation(error.message));
  }
}
module.exports = {
  handleCommonUpload,
  sendEmail,
  validateCode,
  getStatistics,
  getUserGrowthTrend,
  getPostPublishTrend
}